--Purpose: Read the partner information from the Kiva website, and print a comma separated list of partner_id and
--  average interest rate to DBMS_OUTPUT.
--Note: Since it is dependent on the website HTML this function will be highly likely to fail.
--Manual: Adjust the high number in the for loop.  It should be above the highest id.  See 
--  http://api.kivaws.org/v1/partners.xml to find the last ID.
declare
  httpuri httpUriType;
  url varchar2(100);
  partnerClob clob;
  relevantHtml varchar2(100);
  openTDPosition number;
  closeTDPosition number;
begin
  --Loop through all of the partners
  for partner_id in 1 .. 200 loop
    --Create a URL for each partner ID
    url := 'http://www.kiva.org/about/aboutPartner?id='||to_char(partner_id);

    --TEST
    --dbms_output.put_line('Loading url '||url);

    --Download the file, convert to CLOB    
    httpuri := HttpUriType(url);
    dbms_lob.createtemporary(partnerClob, true);
    partnerClob := httpuri.getClob();

    --Grab the interest rate.  The HTML looks something like this:
    --<th><a  class="help" href="javascript:pop('app.php?page=help&amp;action=InterestRateComparison#AverageInterestRateBorrowerPaysToKivaFieldPartner', 'Kiva_Help', 500, 500);" >Average Interest Rate Borrower Pays To Kiva Field Partner</a></th>
    --<td> N/A </td>
    --<td>23.30%</td>
    --  </tr>
    --
    --Only get what's inbetween the first <td></td>
    relevantHtml := dbms_lob.substr(partnerClob, 50, dbms_lob.instr(partnerClob
      , 'Average Interest Rate Borrower Pays To Kiva Field Partner')+40);

    --Find the open and close tag
    openTDPosition := instr(relevantHtml, '<td>');
    closeTDPosition := instr(relevantHtml, '</td>');
    --Get data between the two tags.  Remove spaces
    relevantHtml := trim(substr(relevantHtml, openTDPosition+4, closeTDPosition - openTDPosition - 4));
    --Remove percent sign
    relevantHtml := replace(relevantHtml, '%');

    --Only print some results if there is data
    if relevantHtml is not null then
      --Convert N/A to null, add partner_id to front, and add coma
      if relevantHtml = 'N/A' then
        relevantHtml := to_char(partner_id)||',';
      else
        relevantHtml := to_char(partner_id)||','||relevantHtml;
      end if;

      --Print to DBMS_OUTPUT
      dbms_output.put_line(relevantHtml);
    end if;

  end loop; --each partner
end;
/


/*
begin
  partners xmltype;
  numberOfPages number;
  partnerIndex number;
  v_partner partner%rowtype;
  countryIndex number;
  v_country country%rowtype;
  pairs varchar2(100);
begin
  --Load the first partner page
  partners := getXMLFromURL('http://api.kivaws.org/v1/partners.xml&page=1');

  --get the number of pages
  numberOfPages := to_number(extractAndGetStringVal(partners, '/response/paging/pages/text()'));
  
  --Loop through all pages
  for pageIndex in 1 .. numberOfPages loop
    --Load the new partner page (don't reload the first page)
    if pageIndex > 1 then
      partners := getXMLFromURL('http://api.kivaws.org/v1/partners.xml&page='||to_char(numberOfPages));
    end if;

    --Reset the partner index
    partnerIndex := 0;

    --Loop through all partners
    loop
      --Get the partner_id to see if this partner exists
      partnerIndex := partnerIndex + 1;
      v_partner.id := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/id/text()');

      --Stop looping when we hit the first null partner_id
      exit when v_partner.id is null;

      --Load all of the fields
      v_partner.name := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/name/text()');
      v_partner.status := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/status/text()');
      v_partner.rating := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/rating/text()');
      v_partner.image_id := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/image/id/text()');
      v_partner.template_id := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/image/template_id/text()');
      v_partner.start_date := iso8601ToDate(extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/start_date/text()'));
      v_partner.delinquency_rate := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/delinquency_rate/text()');
      v_partner.default_rate := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/default_rate/text()');
      v_partner.total_amount_raised := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/total_amount_raised/text()');
      v_partner.loans_posted := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/loans_posted/text()');

      --Insert the partner record
      insert into partner values v_partner;
      
      --Reset the country index
      countryIndex := 0;

      --Loop through all the related countries
      loop
        --v_country := v_country();

        --Get the country_name to see if the country exists
        countryIndex := countryIndex + 1;
        v_country.name := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/name/text()');
        --Remove some HTML characters.  For example, Cote D&apos;Ivoire should be Cote D'Ivoire.
        v_country.name := replace(v_country.name, '&apos;', '''');

        --Stop looping when we hit the first null country name
        exit when v_country.name is null;
  
        --Load all of the fields
        v_country.iso_code := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/iso_code/text()');
        v_country.region := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/region/text()');
        --Pairs are stored as "latitude longitude".  So get numbers before and after first space.
        pairs := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/location/geo/pairs/text()');
        v_country.latitude := substr(pairs, 1, instr(pairs, ' ')-1 );
        v_country.longitude := substr(pairs, instr(pairs, ' ')+1 );

        --Add row to region if necessary
        insert into region
        (select v_country.region, null, null
         from dual
         where v_country.region not in (select name from region));

        --dbms_output.put_line('country.name = '||v_country.name||', iso_code = '||v_country.iso_code);

        --Add the row to country if necessary
        insert into country (name, iso_code, region, latitude, longitude)
        (select v_country.name, v_country.iso_code, v_country.region, v_country.latitude, v_country.longitude
         from dual
         where v_country.name not in (select name from country));

        --Add the row to partner_country
        --insert into partner_country values (v_partner.id, v_country.name);
      end loop; --each country
    end loop; --each partner
  end loop; --each page

end loadPartner;
*/